﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace QuanLiHocSinh
{
    public partial class TongKet : Form
    {
        OleDbConnection connect;
        public TongKet()
        {
            InitializeComponent();

            //===========================================================================================


            connect = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=CSDL.mdb");

            if (connect != null)
            {
                connect.Open();

                //===========================

                string point = "select Mon from monhoc";
                OleDbCommand comman = new OleDbCommand(point, connect);

                OleDbDataReader reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    comboBox1.Items.Add(reader.GetString(0));
                }

                //===========================
            }
            else
            {
                MessageBox.Show("Không kết nối được với CSDL", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }

        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

            if (comboBox2.Text != "")
            {
                //===========clear============
                listView1.Items.Clear();
                //============================
                int stt = 1;
                string lop = "select * from Lop";
                OleDbCommand comman = new OleDbCommand(lop, connect);
                OleDbDataReader reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    float siso = 0;
                    float dat = 0;
                    //=======================================
                    listView1.Items.Add(stt.ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetString(0));
                    //===============siso====================
                    string si_so = "select count(*) from hocsinh where Lop = '" + reader.GetString(0) + "'";
                    OleDbCommand call = new OleDbCommand(si_so, connect);
                    OleDbDataReader rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        siso = (float)rd.GetInt32(0);
                        listView1.Items[stt - 1].SubItems.Add(rd.GetInt32(0).ToString());
                    }
                    //===============sldat===================
                    string ten_bang_mon = "";
                    string get_ma_mon = "select MaMon from monhoc where Mon = '" + comboBox1.Text + "'";
                    call = new OleDbCommand(get_ma_mon, connect);
                    rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        ten_bang_mon = rd.GetString(0);
                    }
                    string sldat = "select count(*) from " + ten_bang_mon + " where HocKy = " + int.Parse(comboBox2.Text) + " and Lop = '" + reader.GetString(0) + "' and DatMon = 1";
                    call = new OleDbCommand(sldat, connect);
                    rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        dat = (float)rd.GetInt32(0);
                        listView1.Items[stt - 1].SubItems.Add(rd.GetInt32(0).ToString());
                    }
                    //================tile===================
                    if (siso != 0)
                    {
                        float tile = dat * 100 / siso;
                        string tl = tile.ToString();
                        if (tl.Length > 5)
                        {
                            string tl2 = tl.Substring(0, 5);
                            listView1.Items[stt - 1].SubItems.Add(tl2 + "%");
                        }
                        else
                        {
                            listView1.Items[stt - 1].SubItems.Add(tile.ToString() + "%");
                        }
                    }
                    else
                    {
                        listView1.Items[stt - 1].SubItems.Add("0%");
                    }
                    //=======================================
                    stt++;
                }

            }
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text != "")
            {
                //===========clear============
                listView1.Items.Clear();
                //============================
                int stt = 1;
                string lop = "select * from Lop";
                OleDbCommand comman = new OleDbCommand(lop, connect);
                OleDbDataReader reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    float siso = 0;
                    float dat = 0;
                    //=======================================
                    listView1.Items.Add(stt.ToString());
                    listView1.Items[stt - 1].SubItems.Add(reader.GetString(0));
                    //===============siso====================
                    string si_so = "select count(*) from hocsinh where Lop = '" + reader.GetString(0) + "'";
                    OleDbCommand call = new OleDbCommand(si_so, connect);
                    OleDbDataReader rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        siso = (float)rd.GetInt32(0);
                        listView1.Items[stt - 1].SubItems.Add(rd.GetInt32(0).ToString());
                    }
                    //===============sldat===================

                    string ten_bang_mon = "";
                    string get_ma_mon = "select MaMon from monhoc where Mon = '" + comboBox1.Text + "'";
                    call = new OleDbCommand(get_ma_mon, connect);
                    rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        ten_bang_mon = rd.GetString(0);
                    }

                    string sldat = "select count(*) from " + ten_bang_mon + " where HocKy = " + int.Parse(comboBox2.Text) + " and Lop = '" + reader.GetString(0) + "' and DatMon = 1";
                    call = new OleDbCommand(sldat, connect);
                    rd = call.ExecuteReader();
                    while (rd.Read())
                    {
                        dat = (float)rd.GetInt32(0);
                        listView1.Items[stt - 1].SubItems.Add(rd.GetInt32(0).ToString());
                    }
                    //================tile===================
                    if (siso != 0)
                    {
                        float tile = dat * 100 / siso;
                        string tl = tile.ToString();
                        if (tl.Length > 5)
                        {
                            string tl2 = tl.Substring(0, 5);
                            listView1.Items[stt - 1].SubItems.Add(tl2 + "%");
                        }
                        else
                        {
                            listView1.Items[stt - 1].SubItems.Add(tile.ToString() + "%");
                        }
                    }
                    else
                    {
                        listView1.Items[stt - 1].SubItems.Add("0%");
                    }
                    //=======================================
                    stt++;
                }

            }
        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            //===========clear============
            listView2.Items.Clear();
            //============================
            int stt = 1;
            string lop = "select * from Lop";
            OleDbCommand comman = new OleDbCommand(lop, connect);
            OleDbDataReader reader = comman.ExecuteReader();
            while (reader.Read())
            {
                float siso = 0;
                float dat = 0;
                //=======================================
                listView2.Items.Add(stt.ToString());
                listView2.Items[stt - 1].SubItems.Add(reader.GetString(0));
                //===============siso====================
                string si_so = "select count(*) from hocsinh where Lop = '" + reader.GetString(0) + "'";
                OleDbCommand call = new OleDbCommand(si_so, connect);
                OleDbDataReader rd = call.ExecuteReader();
                while (rd.Read())
                {
                    siso = (float)rd.GetInt32(0);
                    listView2.Items[stt - 1].SubItems.Add(rd.GetInt32(0).ToString());
                }
                //==============sldat====================
                string hocsinh = "select HoTen from hocsinh where Lop = '"+reader.GetString(0)+"'";
                call = new OleDbCommand(hocsinh, connect);
                rd = call.ExecuteReader();
                while (rd.Read())
                {
                    
                    float tong_TB = 0;
                    string mon = "select MaMon from monhoc";
                    OleDbCommand cmm = new OleDbCommand(mon, connect);
                    OleDbDataReader rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        string kq = "select DiemTB from "+rdd.GetString(0)+" where HoTen = '"+rd.GetString(0)+"' and Lop = '"+reader.GetString(0)+"' and HocKy = "+int.Parse(comboBox3.Text)+"";
                        OleDbCommand cmmm = new OleDbCommand(kq, connect);
                        OleDbDataReader rddd = cmmm.ExecuteReader();
                        
                        while (rddd.Read())
                        {
                            tong_TB += rddd.GetFloat(0);
                        }
                    }
                    float tong_mon = 0;
                    float dat_mon = 0;
                    string tm = "select count(*) from monhoc";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        tong_mon = (float)rdd.GetInt32(0);
                    }
                    tm = "Select DiemDatMon from rule";
                    cmm = new OleDbCommand(tm, connect);
                    rdd = cmm.ExecuteReader();
                    while (rdd.Read())
                    {
                        dat_mon = rdd.GetFloat(0);
                    }
                    if (tong_TB / tong_mon >= dat_mon)
                    {
                        dat++;
                    }
                }
                listView2.Items[stt - 1].SubItems.Add(dat.ToString());
                //================tile===================
                if (siso != 0)
                {
                    float tile = dat * 100 / siso;
                    string tl = tile.ToString();
                    if (tl.Length > 5)
                    {
                        string tl2 = tl.Substring(0, 5);
                        listView2.Items[stt - 1].SubItems.Add(tl2 + "%");
                    }
                    else
                    {
                        listView2.Items[stt - 1].SubItems.Add(tile.ToString() + "%");
                    }
                }
                else
                {
                    listView2.Items[stt - 1].SubItems.Add("0%");
                }
                //=======================================
                stt++;
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
